package org.mcjug.locationmailer.db;

import java.util.Date;

import org.mcjug.locationmailer.R;
import org.mcjug.locationmailer.model.StatusMessage;
import org.mcjug.locationmailer.util.Constants;
import org.mcjug.locationmailer.util.ExceptionUtil;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.os.Build;

public class StatusMessageDatabaseHelper {
    private static final String TAG = StatusMessageDatabaseHelper.class.getSimpleName();

    private String databaseName;
    private String tableName;

    // Status Message column names
    public String columnId;
    public String columnLatitude;
    public String columnLongtitude;
    public String columnMessage;
    public String columnAddress;
    public String columnCreatedDate;
    public String columnLocationProvider;

    private String ORDER_BY_CREATED_DATE;

    private int dbVersion;
    private DbHelper dbHelper;
    private Context context;

    public StatusMessageDatabaseHelper(Context context) {
        try {
            databaseName = Constants.getProperty(context, R.string.databaseNamePreferenceName,
                    R.string.databaseNameDefaultValue);
            tableName = Constants
                    .getProperty(context, R.string.tableNamePreferenceName, R.string.tableNameDefaultValue);
            columnId = Constants.getProperty(context, R.string.columnIdPreferenceName, R.string.columnIdDefaultValue);

            columnLatitude = Constants.getProperty(context, R.string.columnLatitudePreferenceName,
                    R.string.columnLatitudeDefaultValue);
            columnLongtitude = Constants.getProperty(context, R.string.columnLongitudePreferenceName,
                    R.string.columnLongitudeDefaultValue);
            columnMessage = Constants.getProperty(context, R.string.columnMessagePreferenceName,
                    R.string.columnMessageDefaultValue);
            columnAddress = Constants.getProperty(context, R.string.columnAddressPreferenceName,
                    R.string.columnAddressDefaultValue);
            columnCreatedDate = Constants.getProperty(context, R.string.columnCreatedDatePreferenceName,
                    R.string.columnCreatedDateDefaultValue);
            columnLocationProvider = Constants.getProperty(context, R.string.columnLocationProviderPreferenceName,
                    R.string.columnLocationProviderDefaultValue);

            ORDER_BY_CREATED_DATE = columnCreatedDate + " DESC";
            dbVersion = context.getResources().getInteger(R.integer.dbVersion);
            this.dbHelper = new DbHelper(context);
            this.context = context;
        } catch (Throwable e) {
            ExceptionUtil.exceptAway(context, e, "Error in StatusMessageDatabaseHelper(Context context)", null);
        }
    }

    public void insertOrIgnore(StatusMessage statusMessage) throws Throwable {
        ContentValues values = getContentValues(statusMessage);
        SQLiteDatabase db = this.dbHelper.getWritableDatabase();
        try {
            if (Build.VERSION.SDK_INT == Build.VERSION_CODES.ECLAIR_MR1) {
                // works on Android 2.1
                db.insertOrThrow(tableName, null, values);
            } else {
                db.insertWithOnConflict(tableName, null, values, SQLiteDatabase.CONFLICT_IGNORE);
            }
        } finally {
            db.close();
        }
    }

    public long insertOrThrow(StatusMessage statusMessage) throws Throwable {
        long id = -1;
        ContentValues values = getContentValues(statusMessage);
        SQLiteDatabase db = this.dbHelper.getWritableDatabase();
        try {
            id = db.insertOrThrow(tableName, null, values);
        } finally {
            db.close();
        }
        return id;
    }

    public ContentValues getContentValues(StatusMessage statusMessage) throws Throwable {
        ContentValues values = new ContentValues();
        Location location = statusMessage.getLocation();
        if (location == null) {
            values.putNull(columnLatitude);
            values.putNull(columnLongtitude);
            values.putNull(columnLocationProvider);
        } else {
            values.put(columnLatitude, String.valueOf(location.getLatitude()));
            values.put(columnLongtitude, String.valueOf(location.getLongitude()));
            values.put(columnLocationProvider, location.getProvider());
        }

        values.put(columnMessage, statusMessage.getMessage());
        values.put(columnAddress, statusMessage.getAddress());
        values.put(columnCreatedDate, statusMessage.getCreatedDate().getTime());

        return values;
    }

    public Cursor getStatusMessages(int limitNum) throws Throwable {
        SQLiteDatabase db = this.dbHelper.getReadableDatabase();
        // Return cursor where columns are
        return db.query(tableName, null, null, null, null, null, ORDER_BY_CREATED_DATE + " Limit " + limitNum);
    }

    public StatusMessage getLatestStatusMessage() throws Throwable {
        SQLiteDatabase db = this.dbHelper.getReadableDatabase();
        StatusMessage statusMessage = null;
        try {
            Cursor cursor = db.query(tableName, null, null, null, null, null, ORDER_BY_CREATED_DATE + " Limit 1");
            try {
                if (cursor.moveToFirst()) {
                    statusMessage = new StatusMessage();
                    statusMessage.setId(cursor.getLong(cursor.getColumnIndex(columnId)));

                    Location location = null;
                    String locationProvider = cursor.getString(cursor.getColumnIndex(columnLocationProvider));
                    if (locationProvider != null) {
                        location = new Location(locationProvider);
                        String latitude = String.format(context.getString(R.string.latitudeFormatted),
                                cursor.getString(cursor.getColumnIndex(columnLatitude)));
                        String longitude = String.format(context.getString(R.string.longitudeFormatted),
                                cursor.getString(cursor.getColumnIndex(columnLongtitude)));

                        location.setLatitude(Double.parseDouble(latitude));
                        location.setLongitude(Double.parseDouble(longitude));
                    }

                    statusMessage.setLocation(location);
                    statusMessage.setMessage(cursor.getString(cursor.getColumnIndex(columnMessage)));
                    statusMessage.setAddress(cursor.getString(cursor.getColumnIndex(columnAddress)));
                    statusMessage.setCreatedDate(new Date(cursor.getLong(cursor.getColumnIndex(columnCreatedDate))));
                }
            } finally {
                cursor.close();
            }
        } finally {
            db.close();
        }
        return statusMessage;
    }

    public void delete() throws Throwable {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        try {
            db.delete(tableName, null, null);
        } finally {
            db.close();
        }
    }

    public SQLiteDatabase getReadableDatabase() throws Throwable {
        return dbHelper.getReadableDatabase();
    }

    public SQLiteDatabase getWritableDatabase() throws Throwable {
        return dbHelper.getWritableDatabase();
    }

    public void close() throws Throwable {
        this.dbHelper.close();
    }

    // DbHelper implementations
    public class DbHelper extends SQLiteOpenHelper {
        public DbHelper(Context context) {
            super(context, databaseName, null, dbVersion);
        }

        public void onCreate(SQLiteDatabase db) {
            String sql = String
                    .format("create table %s (%s integer primary key AUTOINCREMENT, %s VARCHAR, %s VARCHAR, %s VARCHAR, %s VARCHAR, %s VARCHAR, %s long)",
                            tableName, columnId, columnLatitude, columnLongtitude, columnLocationProvider,
                            columnMessage, columnAddress, columnCreatedDate);
            db.execSQL(sql);
        }

        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("drop table " + tableName);
            this.onCreate(db);
        }
    }
}
